const { pool } = require('../config/db');

/**
 * 审核服务
 */
class AuditService {
  /**
   * 获取待审核的记录列表（已处理但未审核的）
   * @param {number} page - 页码
   * @param {number} pageSize - 每页数量
   * @returns {Promise<Object>} - 分页结果
   */
  async getPendingAuditRecords(page = 1, pageSize = 20) {
    try {
      const offset = (page - 1) * pageSize;

      // 查询总数
      const [countResult] = await pool.execute(
        'SELECT COUNT(*) as total FROM raw_personnel WHERE process_status = ? AND audit_status = ?',
        ['completed', 'pending']
      );
      const total = countResult[0].total;

      // 查询数据
      const [rows] = await pool.execute(
        `SELECT * FROM raw_personnel 
         WHERE process_status = ? AND audit_status = ?
         ORDER BY process_time DESC
         LIMIT ? OFFSET ?`,
        ['completed', 'pending', pageSize, offset]
      );

      return {
        list: rows,
        total,
        page,
        pageSize,
        totalPages: Math.ceil(total / pageSize)
      };
    } catch (error) {
      console.error('获取待审核记录失败:', error.message);
      throw error;
    }
  }

  /**
   * 根据ID获取原始记录和处理后的记录
   * @param {number} id - 原始人员ID
   * @returns {Promise<Object>} - 原始记录和处理后记录
   */
  async getRecordWithProcessedData(id) {
    try {
      // 获取原始记录
      const [rawRows] = await pool.execute(
        'SELECT * FROM raw_personnel WHERE id = ?',
        [id]
      );

      if (rawRows.length === 0) {
        return {
          success: false,
          message: '记录不存在'
        };
      }

      const rawRecord = rawRows[0];

      // 获取处理后的记录
      const [processedRows] = await pool.execute(
        'SELECT * FROM processed_personnel WHERE raw_personnel_id = ?',
        [id]
      );

      return {
        success: true,
        rawRecord,
        processedRecord: processedRows[0] || null
      };
    } catch (error) {
      console.error('获取记录详情失败:', error.message);
      throw error;
    }
  }

  /**
   * 审核记录
   * @param {number} id - 原始人员ID
   * @param {string} status - 审核状态 (approved/rejected)
   * @param {string} auditorName - 审核人姓名
   * @param {string} auditorId - 审核人ID
   * @param {string} comment - 审核意见（可选）
   * @param {Object} updatedData - 编辑后的数据（可选）
   * @returns {Promise<Object>} - 审核结果
   */
  async auditRecord(id, status, auditorName, auditorId, comment = null, updatedData = null) {
    const connection = await pool.getConnection();
    try {
      await connection.beginTransaction();

      if (!['approved', 'rejected'].includes(status)) {
        await connection.rollback();
        return {
          success: false,
          message: '无效的审核状态'
        };
      }

      // 检查记录是否存在且已处理
      const [rows] = await connection.execute(
        'SELECT * FROM raw_personnel WHERE id = ? AND process_status = ?',
        [id, 'completed']
      );

      if (rows.length === 0) {
        await connection.rollback();
        return {
          success: false,
          message: '记录不存在或未完成处理'
        };
      }

      // 如果有编辑后的数据，先更新processed_personnel表
      if (updatedData) {
        // 格式化日期函数：将ISO格式或其他格式转为YYYY-MM-DD
        const formatDateForDB = (dateValue) => {
          if (!dateValue) return null;
          
          try {
            // 如果是ISO格式的日期时间字符串，提取日期部分
            if (typeof dateValue === 'string') {
              // ISO格式：2024-01-01T00:00:00.000Z -> 2024-01-01
              if (dateValue.includes('T')) {
                return dateValue.split('T')[0];
              }
              // 已经是YYYY-MM-DD格式
              if (/^\d{4}-\d{2}-\d{2}$/.test(dateValue)) {
                return dateValue;
              }
            }
            return dateValue;
          } catch (error) {
            console.warn('日期格式化失败:', dateValue, error);
            return null;
          }
        };

        // 处理数据：空值转null，日期格式化
        const processedData = {
          chinese_name: updatedData.chinese_name || null,
          english_name: updatedData.english_name || null,
          country_region: updatedData.country_region || null,
          gender: updatedData.gender || null,
          birth_date: formatDateForDB(updatedData.birth_date),
          age: updatedData.age || null,
          email: updatedData.email || null,
          phone: updatedData.phone || null,
          graduate_school: updatedData.graduate_school || null,
          education_level: updatedData.education_level || null,
          military_branch: updatedData.military_branch || null,
          military_rank: updatedData.military_rank || null,
          organization: updatedData.organization || null,
          current_position: updatedData.current_position || null,
          position_start_date: formatDateForDB(updatedData.position_start_date),
          work_status: updatedData.work_status || null,
          work_experience_cn: updatedData.work_experience_cn || null,
          education_experience_cn: updatedData.education_experience_cn || null
        };

        const updateSql = `
          UPDATE processed_personnel
          SET 
            chinese_name = ?,
            english_name = ?,
            country_region = ?,
            gender = ?,
            birth_date = ?,
            age = ?,
            email = ?,
            phone = ?,
            graduate_school = ?,
            education_level = ?,
            military_branch = ?,
            military_rank = ?,
            organization = ?,
            current_position = ?,
            position_start_date = ?,
            work_status = ?,
            work_experience_cn = ?,
            education_experience_cn = ?,
            updated_at = NOW()
          WHERE raw_personnel_id = ?
        `;

        await connection.execute(updateSql, [
          processedData.chinese_name,
          processedData.english_name,
          processedData.country_region,
          processedData.gender,
          processedData.birth_date,
          processedData.age,
          processedData.email,
          processedData.phone,
          processedData.graduate_school,
          processedData.education_level,
          processedData.military_branch,
          processedData.military_rank,
          processedData.organization,
          processedData.current_position,
          processedData.position_start_date,
          processedData.work_status,
          processedData.work_experience_cn,
          processedData.education_experience_cn,
          id
        ]);
      }

      // 更新审核信息
      await connection.execute(
        `UPDATE raw_personnel 
         SET audit_status = ?, audit_time = NOW(), 
             auditor_name = ?, auditor_id = ?, audit_comment = ?
         WHERE id = ?`,
        [status, auditorName, auditorId, comment, id]
      );

      await connection.commit();

      console.log(`审核完成: ID=${id}, 状态=${status}, 审核人=${auditorName}`);

      return {
        success: true,
        message: '审核成功',
        auditStatus: status
      };
    } catch (error) {
      await connection.rollback();
      console.error('审核记录失败:', error.message);
      throw error;
    } finally {
      connection.release();
    }
  }

  /**
   * 批量审核
   * @param {Array} auditList - 审核列表 [{id, status, comment}]
   * @param {string} auditorName - 审核人姓名
   * @param {string} auditorId - 审核人ID
   * @returns {Promise<Object>} - 批量审核结果
   */
  async batchAudit(auditList, auditorName, auditorId) {
    try {
      const results = [];

      for (const item of auditList) {
        const result = await this.auditRecord(
          item.id,
          item.status,
          auditorName,
          auditorId,
          item.comment
        );
        results.push({
          id: item.id,
          ...result
        });
      }

      const successCount = results.filter(r => r.success).length;
      const failCount = results.filter(r => !r.success).length;

      return {
        success: true,
        totalCount: auditList.length,
        successCount,
        failCount,
        details: results
      };
    } catch (error) {
      console.error('批量审核失败:', error.message);
      throw error;
    }
  }

  /**
   * 获取审核统计信息
   * @returns {Promise<Object>} - 统计信息
   */
  async getAuditStatistics() {
    try {
      const [stats] = await pool.execute(
        `SELECT 
          COUNT(*) as total,
          SUM(CASE WHEN process_status = 'completed' AND audit_status = 'pending' THEN 1 ELSE 0 END) as pending_audit,
          SUM(CASE WHEN audit_status = 'approved' THEN 1 ELSE 0 END) as approved,
          SUM(CASE WHEN audit_status = 'rejected' THEN 1 ELSE 0 END) as rejected
         FROM raw_personnel
         WHERE process_status = 'completed'`
      );

      return {
        total: stats[0].total || 0,
        pending: stats[0].pending_audit || 0,
        approved: stats[0].approved || 0,
        rejected: stats[0].rejected || 0
      };
    } catch (error) {
      console.error('获取审核统计失败:', error.message);
      throw error;
    }
  }

  /**
   * 获取已审核记录列表（包含处理后的数据）
   * @param {string} auditStatus - 审核状态 (approved/rejected/all)
   * @param {number} page - 页码
   * @param {number} pageSize - 每页数量
   * @returns {Promise<Object>} - 分页结果
   */
  async getAuditedRecords(auditStatus = 'all', page = 1, pageSize = 20) {
    try {
      const offset = (page - 1) * pageSize;

      let whereClause = 'WHERE r.process_status = ? AND r.audit_status != ?';
      let params = ['completed', 'pending', pageSize, offset];

      if (auditStatus !== 'all') {
        whereClause = 'WHERE r.process_status = ? AND r.audit_status = ?';
        params = ['completed', auditStatus, pageSize, offset];
      }

      // 查询总数
      const countParams = params.slice(0, -2);
      const [countResult] = await pool.execute(
        `SELECT COUNT(*) as total 
         FROM raw_personnel r
         INNER JOIN processed_personnel p ON r.id = p.raw_personnel_id
         ${whereClause}`,
        countParams
      );
      const total = countResult[0].total;

      // 查询数据（包含处理后的所有信息，用于空值率统计）
      const [rows] = await pool.execute(
        `SELECT 
          r.id,
          r.audit_status,
          r.audit_time,
          r.auditor_name,
          r.auditor_id,
          r.audit_comment,
          p.country_region,
          p.chinese_name,
          p.english_name,
          p.gender,
          p.birth_date,
          p.age,
          p.email,
          p.phone,
          p.graduate_school,
          p.education_level,
          p.military_branch,
          p.military_rank,
          p.organization,
          p.current_position,
          p.position_start_date,
          p.avatar_path,
          p.english_biography,
          p.chinese_biography,
          p.work_experience_cn,
          p.education_experience_cn,
          p.work_status,
          p.data_source
         FROM raw_personnel r
         INNER JOIN processed_personnel p ON r.id = p.raw_personnel_id
         ${whereClause}
         ORDER BY r.audit_time DESC
         LIMIT ? OFFSET ?`,
        params
      );

      return {
        success: true,
        data: rows,
        pagination: {
          page,
          pageSize,
          total,
          totalPages: Math.ceil(total / pageSize)
        }
      };
    } catch (error) {
      console.error('获取已审核记录失败:', error.message);
      throw error;
    }
  }
}

module.exports = new AuditService();

